package cn.com.open.itembank.sqlprovider;

import cn.com.open.itembank.common.enums.SubjectiveSearchScopeType;
import cn.com.open.itembank.dto.FilterModelDto;
import cn.com.open.itembank.dto.QuestionCountFilterModel;
import cn.com.open.itembank.utils.StringUtil;

import java.util.Map;

/**
 * Created by lucky on 2017/9/1.
 */
public class QuestionReadSqlProvider {

    private static String baseQuestionCheckParam = " WHERE q.IsCheck =1 AND q.Status = 0 AND qur.ItemBankID ='%s' ";

    private static String baseQuestionQueryRelation = " FROM Question q INNER JOIN QuestionUseRelation qur ON  q.QuestionID = qur.QuestionID  AND qur.Status = 0";

    private static String selectString = " SELECT ";

    //region 私有方法

    /**
     * 从参数列表中获取题库id.
     *
     * @param parameter the parameter
     * @return the item bank
     */
    private String getItemBank(Map<String, Object> parameter) {
        return (String) parameter.get("itemBankId");
    }

    /**
     * 获取试题列表查询用基础表关联信息.
     *
     * @return the question search table
     */
    private String getQuestionSearchTable() {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append(selectString);
        sbSql.append(" q.QuestionID,q.QuestionTypeID,q.QuestionCategory,q.Difficulty,q.Title,q.Comment,q.Score,q.Keywords,q.QuestionAnalysis,");
        sbSql.append(" q.FieldCount,q.Creater,q.CreateTime,q.IsCheck,q.Checker,q.CheckTime,q.LastUpdateTime, q.DisplayCount,q.DisplayTime,q.Source,");
        sbSql.append(" q.IsCourseOnly,q.InputType,q.ImportType,q.SourceType,q.ParentQuestionID,q.IsSubQuestion,q.SequenceIndex,q.IsSubjective,q.Shuffle,");
        sbSql.append(" q.SerialNumber,q.QuestionVersion,q.Difference,q.Perceiver,q.MasterLevel,q.ResponseTime,q.QuestionInnerTypeID,q.Modifier,");
        sbSql.append(" q.ModifyTime,q.MediaType,q.MediaDuration,q.IsPublish,q.PublisherID,q.PublishTime,q.QualityGrade,q.UseType,qur.Status,");
        sbSql.append(" qc.ChapterID,qur.ItemBankID,qtd.QuestionTypeName,");
        sbSql.append(" ISNULL(PaperRelation.UseCnt, 0) + ISNULL(RuleRelation.UseCnt, 0) UseCnt,qconent.ContentXml,cter.ChapterName");
        sbSql.append(" FROM Question q");
        sbSql.append(" INNER JOIN QuestionUseRelation qur ON q.QuestionID = qur.QuestionID");
        sbSql.append(" INNER JOIN QuestionChapterInfo qc ON qc.ItemBankID = qur.ItemBankID AND qc.QuestionID = q.QuestionID");
        sbSql.append(" INNER JOIN QuestionTypeDefine qtd ON qtd.QuestionTypeID = q.QuestionTypeID");
        sbSql.append(" INNER JOIN QuestionContent qconent ON q.QuestionID=qconent.QuestionID");
        sbSql.append(" LEFT JOIN Chapter cter ON qc.ChapterID=cter.ChapterID");
        sbSql.append(" LEFT JOIN (SELECT QuestionID, COUNT(1) UseCnt FROM QuestionDisplayInfo GROUP BY QuestionID) PaperRelation ON q.QuestionID = PaperRelation.QuestionID");
        sbSql.append(" LEFT JOIN (SELECT QuestionID, COUNT(1) UseCnt FROM QuestionRule GROUP BY QuestionID) RuleRelation ON q.QuestionID = RuleRelation.QuestionID");
        sbSql.append(" WHERE 1=1 ");
        return sbSql.toString();
    }

    /**
     * 获取回收站列表查询用基础表关联信息.
     *
     * @return the recyle bin search table
     */
    private String getRecyleBinSearchTable() {
        StringBuilder sbSql = new StringBuilder();
        sbSql.append(selectString);
        sbSql.append(" q.QuestionID,q.QuestionTypeID,q.QuestionCategory,q.Difficulty,q.Title,q.Comment,q.Score,q.Keywords,q.QuestionAnalysis,");
        sbSql.append(" q.FieldCount,q.Creater,q.CreateTime,q.IsCheck,q.Checker,q.CheckTime,q.LastUpdateTime, q.DisplayCount,q.DisplayTime,q.Source,");
        sbSql.append(" q.IsCourseOnly,q.InputType,q.ImportType,q.SourceType,q.ParentQuestionID,q.IsSubQuestion,q.SequenceIndex,q.IsSubjective,q.Shuffle,");
        sbSql.append(" q.SerialNumber,q.QuestionVersion,q.Difference,q.Perceiver,q.MasterLevel,q.ResponseTime,q.QuestionInnerTypeID,q.Modifier,");
        sbSql.append(" q.ModifyTime,q.MediaType,q.MediaDuration,q.IsPublish,q.PublisherID,q.PublishTime,q.QualityGrade,q.UseType,qur.Status,");
        sbSql.append(" qc.ChapterID,qur.ItemBankID,qtd.QuestionTypeName,0 AS UseCnt ");
        sbSql.append(" FROM Question q ");
        sbSql.append(" INNER JOIN QuestionUseRelation qur ON q.QuestionID = qur.QuestionID AND qur.status=-1 ");
        sbSql.append(" INNER JOIN QuestionChapterInfo qc ON qc.ItemBankID = qur.ItemBankID AND qc.QuestionID = q.QuestionID ");
        sbSql.append(" INNER JOIN QuestionTypeDefine qtd ON qtd.QuestionTypeID = q.QuestionTypeID ");
        sbSql.append(" WHERE 1=1 ");
        return sbSql.toString();
    }
    //endregion

    //region 根据题库id获取指定题库下试题按照题型，难度，应用类别的数量统计列表sql

    /**
     * 根据题库id获取指定题库下试题按照题型，难度，应用类别的数量统计列表sql.
     *
     * @param parameter the parameter
     * @return the question count by item bank id
     */
    public String getQuestionStatByItemBankId(Map<String, Object> parameter) {
        String itemBankId = getItemBank(parameter);

        StringBuilder sbSql = new StringBuilder();
        sbSql.append(" SELECT qt.[SectionID],qt.QuestionTypeID,qt.QuestionTypeName,qt.QuestionInnerTypeID,Difficulty ");
        sbSql.append(" ,qc.QuestionCategoryID,qc.QuestionCategoryName ,COUNT(*) CNT ");
        sbSql.append(baseQuestionQueryRelation);
        sbSql.append(" INNER JOIN QuestionTypeDefine qt ON qt.QuestionTypeID = q.QuestionTypeID ");
        sbSql.append(" INNER JOIN QuestionTypeUseRelation sqt ON qt.QuestionTypeID = sqt.QuestionTypeID");
        sbSql.append(" INNER JOIN QuestionCategoryRelation qcr ON qcr.QuestionID = q.QuestionID ");
        sbSql.append(" INNER JOIN QuestionCategory qc ON qc.QuestionCategoryID = qcr.QuestionCategoryID ");
        sbSql.append(String.format(baseQuestionCheckParam, itemBankId));
        sbSql.append(" GROUP BY qt.[SectionID],qt.QuestionTypeID, qt.QuestionTypeName, qt.QuestionInnerTypeID,Difficulty");
        sbSql.append(" , qc.QuestionCategoryID, qc.QuestionCategoryName ");
        sbSql.append(" ORDER BY qt.[SectionID],qt.QuestionTypeID, qt.QuestionTypeName, qt.QuestionInnerTypeID,Difficulty");
        sbSql.append(" , qc.QuestionCategoryID, qc.QuestionCategoryName");

        return sbSql.toString();
    }
    //endregion

    //region 根据题库id获取指定题库下试题按照应用类别的数量统计列表sql

    /**
     * 根据题库id获取指定题库下试题按照应用类别的数量统计列表sql.
     *
     * @param parameter the parameter
     * @return the question stat for category by item bank id
     */
    public String getQuestionStatForCategoryByItemBankId(Map<String, Object> parameter) {
        String itemBankId = getItemBank(parameter);

        StringBuilder sbSql = new StringBuilder();
        sbSql.append(" SELECT qc.QuestionCategoryID id,qc.QuestionCategoryName name,COUNT(*) count ");
        sbSql.append(baseQuestionQueryRelation);
        sbSql.append(" INNER JOIN QuestionCategoryRelation qcr ON qcr.QuestionID = q.QuestionID ");
        sbSql.append(" INNER JOIN QuestionCategory qc ON qc.QuestionCategoryID = qcr.QuestionCategoryID ");
        sbSql.append(String.format(baseQuestionCheckParam, itemBankId));
        sbSql.append(" GROUP BY qc.QuestionCategoryID, qc.QuestionCategoryName ");
        sbSql.append(" ORDER BY qc.QuestionCategoryID, qc.QuestionCategoryName");

        return sbSql.toString();
    }
    //endregion

    //region 根据题库id获取指定题库下指定题型的试题按照给定的章节,应用类别，难度范围的数量统计列表sql

    /**
     * 根据题库id获取指定题库下指定题型的试题按照给定的章节,应用类别，难度范围的数量统计列表sql.
     *
     * @param parameter the parameter
     * @return the question stat for category by item bank id
     */
    public String getQuestionDifficultyStatByItemBankId(Map<String, Object> parameter) {
        String itemBankId = getItemBank(parameter);
        double minDiff = (double) parameter.get("minDiff");
        double maxDiff = (double) parameter.get("maxDiff");
        String questionTypeId = (String) parameter.get("questionTypeId");
        String chapterIds = (String) parameter.get("chapterIds");
        String categoryIds = (String) parameter.get("categoryIds");

        StringBuilder sbSql = new StringBuilder();
        sbSql.append(" SELECT COUNT(q.QuestionID) CNT ");
        sbSql.append(baseQuestionQueryRelation);
        sbSql.append(" INNER JOIN QuestionChapterInfo qc ON qc.ItemBankID = qur.ItemBankID and qc.QuestionID = q.QuestionID ");
        sbSql.append(" INNER JOIN QuestionTypeUseRelation sqt ON q.QuestionTypeID = sqt.QuestionTypeID ");
        sbSql.append(String.format(baseQuestionCheckParam, itemBankId));
        sbSql.append(String.format(" AND Difficulty>=%f AND Difficulty<=%f ", minDiff, maxDiff));
        sbSql.append(String.format(" AND sqt.QuestionTypeID ='%s' ", questionTypeId));
        if (chapterIds.length() > 0 && chapterIds.indexOf('\'') >= 0) {
            sbSql.append(String.format(" AND qc.ChapterID IN (%s) ", chapterIds));
        }
        if (categoryIds.length() > 0 && categoryIds.indexOf('\'') >= 0) {
            sbSql.append(String.format(" AND q.QuestionID IN (SELECT QuestionID FROM QuestionCategoryRelation WHERE QuestionCategoryID IN (%s) ) ", categoryIds));
        }

        return sbSql.toString();
    }
    //endregion

    //region 根据题库id，题型id，所属类别id查询试题id列表sql

    /**
     * 根据题库id，题型id，所属类别id查询试题id列表sql.
     *
     * @param parameter the parameter
     * @return the questions by categories for rules
     */
    public String getQuestionsByCategoriesForRules(Map<String, Object> parameter) {
        String itemBankID = (String) parameter.get("itemBankID");
        String questionTypeID = (String) parameter.get("questionTypeID");
        String questionCategoryID = (String) parameter.get("questionCategoryID");

        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT DISTINCT re.QuestionID");
        sbSql.append(" FROM QuestionCategoryRelation AS re");
        sbSql.append(" INNER JOIN question AS q on q.QuestionID=re.QuestionID");
        sbSql.append(" INNER JOIN QuestionUseRelation AS qur on qur.QuestionID = re.QuestionID AND qur.Status = 0");
        sbSql.append(String.format(" WHERE re.QuestionCategoryID IN (%s) AND qur.ItemBankID = '%s' and q.QuestionTypeID='%s' AND q.Status = 0",
                questionCategoryID, itemBankID, questionTypeID));

        return sbSql.toString();
    }
    //endregion

    //region 根据试题数量查询模型获取指定条件下试题数量（策略用）

    /**
     * 根据试题数量查询模型获取指定条件下试题数量（策略用）
     *
     * @param parameter the parameter
     * @return the question count for rules check
     */
    public String getQuestionCountForRulesCheck(Map<String, Object> parameter) {
        QuestionCountFilterModel model = (QuestionCountFilterModel) parameter.get("filterModel");
        boolean getCount = (boolean) parameter.get("getCount");

        StringBuilder sbSql = new StringBuilder();
        sbSql.append(selectString);
        if (getCount) {
            sbSql.append(" COUNT(1) ");
        } else {
            sbSql.append(" q.QuestionId,q.SerialNumber,q.QuestionTypeID,q.FieldCount,q.MediaType,q.MediaDuration,q.IsSubjective,q.Difficulty,q.displayCount,qc.ChapterId ");
        }
        sbSql.append(" FROM Question q ");
        sbSql.append(" INNER JOIN QuestionUseRelation qur ON qur.QuestionID = q.QuestionID AND qur.Status = 0 ");
        sbSql.append(" INNER JOIN QuestionChapterInfo qc ON qc.QuestionID = q.QuestionID AND qc.ItemBankID = qur.ItemBankID ");
        sbSql.append(String.format(baseQuestionCheckParam, model.getItemBankId()));
        sbSql.append(String.format(" AND q.QuestionTypeID ='%s' ", model.getQuestionTypeID()));
        sbSql.append(String.format(" AND (q.DIFFICULTY BETWEEN %s AND %s) ",
                String.valueOf(model.getMinDifficulty()), String.valueOf(model.getMaxDifficulty())));

        if (model.getFieldcount() > 0) {
            sbSql.append(String.format(" AND q.FIELDCOUNT= %d", model.getFieldcount()));
        }
        if (model.getMandatoryIDs().length() > 0) {
            sbSql.append(String.format(" AND q.QuestionID NOT IN (%s) ", model.getMandatoryIDs()));
        }
        if (model.getChapterIDs().length() > 0) {
            sbSql.append(String.format(" AND qc.ChapterID IN (%s) ", model.getChapterIDs()));
        }
        if (model.getMinDuration() == null) {
            model.setMinDuration(Integer.valueOf(0));
        }
        if (model.getMaxDuration() == null) {
            model.setMaxDuration(Integer.valueOf(0));
        }
        if (model.getMinDuration() != new Integer(0)) {
            sbSql.append(String.format(" AND q.MediaDuration>=%s ", model.getMinDuration()));
        }
        if (model.getMaxDuration() !=  new Integer(0)) {
            sbSql.append(String.format(" AND q.MediaDuration<=%s ", model.getMaxDuration()));
        }
        if (model.getQuestionCategoryIDs().length() > 0) {
            sbSql.append(String.format(" AND q.QuestionID IN (SELECT QuestionID FROM QuestionCategoryRelation WHERE QuestionCategoryID IN (%s)) ", model.getQuestionCategoryIDs()));
        }
        //主观题搜索范围
        if (model.getSubjective() != SubjectiveSearchScopeType.ALL.getIndex()) {
            //试题主客观标记：客观题0，主观题1
            //主观题搜索范围(仅填空题可用：0：主观；1：客观；2：全部)
            sbSql.append(String.format(" AND q.IsSubjective=%d ", model.getSubjective() == 0 ? 1 : 0));
        }

        return sbSql.toString();
    }
    //endregion

    //region 根据试题id获取曝光表和策略试题关系表中使用数量总计

    /**
     * 根据试题id获取曝光表和策略试题关系表中使用数量总计.
     *
     * @param parameter the parameter
     * @return the question relation
     */
    public String getQuestionRelationCount(Map<String, Object> parameter) {
        String id = (String) parameter.get("id");
        String paperId = (String) parameter.get("paperId");

        StringBuilder sbSql = new StringBuilder();
        sbSql.append(" SELECT COUNT(*) FROM ( ");
        sbSql.append(String.format(" SELECT [QuestionID] FROM [QuestionDisplayInfo] WHERE [QuestionID]='%s' ", id));
        if (!StringUtil.isEmpty(paperId)) {
            sbSql.append(String.format(" AND [TestPaperID]!='%s' ", paperId));
        }
        sbSql.append(" UNION ");
        sbSql.append(String.format(" SELECT [QuestionID] FROM dbo.[QuestionRule] WHERE [QuestionID] = '%s' ", id));
        sbSql.append(" ) A ");

        return sbSql.toString();
    }
    //endregion

    //region 获取试题列表

    /**
     * 获取试题列表.
     * pageNumber 当前页码
     * pageSize 每页行数
     * sortName 排序字段
     * sortOrder 排序类型
     * sql 获取查询用sql
     *
     * @param parameter the parameter
     * @return the recyle bin query
     */
    public String getListQuery(Map<String, Object> parameter) {

        FilterModelDto filterModelDto = (FilterModelDto) parameter.get("filterModelDto");
        String sql = (String) parameter.get("sql");
        String type = (String) parameter.get("type");

        Integer pageSize = filterModelDto.getPageSize();
        Integer pageNumber = filterModelDto.getPageNumber();
        String sortName = filterModelDto.getSortName();
        String sortOrder = filterModelDto.getSortOrder();

        String table = getQuestionSearchTable();
        if ("recycle".equalsIgnoreCase(type)) {
            table = getRecyleBinSearchTable();
        }

        String order = " ORDER BY q.[CreateTime] DESC";
        if (!StringUtil.isEmpty(sortName) && !StringUtil.isEmpty(sortOrder) && ("ASC".equalsIgnoreCase(sortOrder) || "DESC".equalsIgnoreCase(sortOrder))) {
            order = " ORDER BY q.[" + sortName + "] " + sortOrder;
        }

        StringBuilder paperSql = new StringBuilder();

        if (pageNumber != null && pageSize != null && pageNumber > 0 && pageSize > 0) {
            paperSql.append(String.format("SELECT TOP %d * FROM " +
                            "( SELECT ROW_NUMBER() OVER (%s) ROW , * " +
                            "FROM (%s) q) DATALIST WHERE ROW > %d",
                    pageSize, order, table + sql, (pageNumber - 1) * pageSize));
        } else {
            paperSql.append(String.format("SELECT * FROM (%s) q %s ", table + sql, order));
        }

        return paperSql.toString();
    }

    /**
     * 获取列表数量.
     *
     * @param parameter the parameter
     * @return the recyle bin count
     */
    public String getListCount(Map<String, Object> parameter) {
        String type = (String) parameter.get("type");
        String sql = (String) parameter.get("sql");

        String table = getQuestionSearchTable();
        if ("recycle".equalsIgnoreCase(type)) {
            table = getRecyleBinSearchTable();
        }

        return String.format("SELECT COUNT(1) FROM (%s) q", table + sql);
    }

    //endregion
}
